<?php
/**
* shogun 1 checkout
*/
session_start();
include_once("config/config.inc.php");
include_once("functions.report.php");

$lsql = "select settings_value from settings where id = '3'";
$lres = mysql_query($lsql);
list($lobbyid)=mysql_fetch_row($lres);

function getStartTime()
{
	$sql = "select datetime from `shift-transactions` where shift = 'start' order by datetime desc";
		
	$res = mysql_query($sql);
	while(list($time) = mysql_fetch_row($res))
	{
		return $time;
	}	
}


function getCheckoutReport($start,$end,$suser_id,$euser_id,$lobbyid)
{
	$sql = "select settings_value from settings where id = '1'";
	$res = mysql_query($sql);
	list($value) = mysql_fetch_row($res);
	$ret.="<div class='report'>";
	$ret.= "<b>".strtoupper($value)."</b><br>";
	$ret.= "<b>ROOM CHECKOUT REPORT</b><br>";
	$ret.= "<b>SHIFT: </b>".getshift($start). "&nbsp;&nbsp;&nbsp;&nbsp;".date("m/d/Y g:i:s A",strtotime($start));
	$ret.= "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>CASHIER: </b>";
	if($suser_id == $euser_id)
	{
		$_sql = "select fullname from users where user_id = '$euser_id'";
		$_res = mysql_query($_sql);
		list($cashier)=mysql_fetch_row($_res);
		$ret.=$cashier;
	}else
	{
		$_sql = "select fullname from users where user_id = '$suser_id'";
		$_res = mysql_query($_sql);
		$ret.=$scashier;
		list($scashier)=mysql_fetch_row($_res);

		$ret.=" - ";

		$_sql = "select fullname from users where user_id = '$euser_id'";
		$_res = mysql_query($_sql);
		list($ecashier)=mysql_fetch_row($_res);
		$ret.=$ecashier;
	}
	
	
	$ret.= "<br>";
	$ret.="</div>";
	$ret.= "<br>";
	$ret.= "<br>";
	$ret.= "<br>";
	$sql = "select * from occupancy a, rooms b 
	where a.actual_checkout >= '$start' 
	and a.actual_checkout <= '$end' 
	and a.room_id = b.room_id
	order by b.room_type_id asc,
	a.rate_id desc,
	a.actual_checkout asc";
	

	$res = mysql_query($sql);

	$ret.= "<table cellpadding=4 class='report' >";
	$ret.= "<tr>";
	$ret.= "<th>RM_TYPE</th>";
	$ret.= "<th>RM_NO</th>";
	$ret.= "<th>HRS</th>";
	$ret.= "<th>RATE</th>";
	$ret.= "<th>OT</th>";	
	$ret.= "<th>DISC</th>";
	$ret.= "<th>INTOT</th>";
	$ret.= "<th>CHECKIN</th>";
	$ret.= "<th>CHECKOUT</th>";
	$ret.= "<th>FOOD</th>";
	$ret.= "<th>BEER</th>";
	$ret.= "<th>MISC</th>";
	$ret.= "<th>ADJUST</th>";
	$ret.= "<th>DEDUCT</th>";
	$ret.= "<th>OT</th>";
	$ret.= "<th>TOTAL</th>";
	$ret.= "</tr>";
/***********
		LOBBY
	************/
	$ret.= "<tr>";
	$ret.= "<td>Lobby</td>";
	$ret.= "<td>Lobby</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";	
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";

	//Lobby Food

	$fnblobbytot	= getLobbyFoodSalesByItemNotIn($lobbyid,$start,$end,array(17,21));
	$beerlobbytot	= getLobbyFoodSalesByItemIn($lobbyid,$start,$end,array(17,21));
	$misclobbytot	= getLobbyMiscSalesByItemNotIn($lobbyid,$start,$end,array(2,3));
	$lobbytotal		= $fnblobbytot + $beerlobbytot + $misclobbytot;


	if($fnblobbytot=='')$fnblobbytot='';
	else $fnblobbytot =number_format($fnblobbytot,2);

	if($beerlobbytot=='')$beerlobbytot='';
	else $beerlobbytot=number_format($beerlobbytot,2);
	
	if($misclobbytot=='')$misclobbytot='';
	else $misclobbytot=number_format($misclobbytot,2);
	
	if($lobbytotal=='0')$lobbytotal='0.00';
	else $lobbytotal=number_format($lobbytotal);
	

	$ret.= "<td>$fnblobbytot</td>";
	$ret.= "<td>$beerlobbytot</td>";
	$ret.= "<td>$misclobbytot</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";
	$ret.= "<td>&nbsp;</td>";

	 $ret.= '<td>'.number_format($lobbytotal,2).'</td>';
	$ret.= "</tr>";
	$origstart = $start;
	while($row = mysql_fetch_array($res))
	{
		//-->
		$start = $origstart;
		$ratehours = 0;
		$weightedstart=0;
		if($row["rate_id"] == 2) {
			$ratehours=12;
		}elseif($row["rate_id"] == 3) {
			$ratehours=24;
		}

		if($ratehours) {
			$weightedstart = date('Y-m-d H:i:s', strtotime("$end - $ratehours hours"));
		}
		if($weightedstart) $start = $weightedstart;
		//-->

		//room_id, occupancy_id, actual_checkin, actual_checkout
		//need to also get site_id to determine where rechit is applied
		$_sql = "select room_type_id,door_name, site_id from rooms where room_id = '".$row["room_id"]."'";
		$_res = mysql_query($_sql);
		list($id,$door_name, $site_id)=mysql_fetch_row($_res);
		$_sql = "select room_type_name from room_types where room_type_id = '$id'";
		$_res = mysql_query($_sql);
		list($room_type_name)=mysql_fetch_row($_res);
	
		$ret .="<tr>";
		//$ret .="<td>".$row["occupancy_id"]."</td>";
		$ret .="<td>".$room_type_name."</td>";
		$ret .="<td>".$door_name."</td>";

		$_sql = "select rate_name, duration from rates where rate_id = '".$row["rate_id"]."'";
		$_res = mysql_query($_sql);
		list($rate_name,$duration)=mysql_fetch_row($_res);
		$rate_name = str_replace("HRS","",$rate_name);
		$ret .="<td>".$rate_name."</td>";

		/*
		* getting room charge here
		*/

		$cutoff = date('Y-m-d H:i:s',strtotime("$start + $duration hours") );

		$_sql = "select unit_cost*qty from room_sales where occupancy_id = '".$row["occupancy_id"]."' 
				and status in ('Paid')
				and item_id = '15'
				order by roomsales_id desc
				limit 0,1";
		
		$_res = mysql_query($_sql);
		list($cost)=mysql_fetch_row($_res);
		
		$ret .="<td>".$cost."</td>";

		$_sql = "select a.unit_cost*a.qty, a.roomsales_id
			FROM room_sales a, occupancy_log b
			WHERE a.occupancy_id = b.occupancy_id
			And a.update_date=b.transaction_date
			AND a.item_id = '16'
			AND b.remarks not in ('Overtime on checkout')
			AND a.status in ('Paid')
			AND a.occupancy_id = '".$row["occupancy_id"]."'";
		//echo $_sql."<hr/>";
		$_res = mysql_query($_sql);
		list($in_ot,$inextid)=mysql_fetch_row($_res);
		if($in_ot=='')
		{
			$din_ot = '';	
		}else{
			$din_ot=$in_ot;
		}	
		$ret .="<td>".$din_ot."</td>";

		$adjtot  = getTotalMiscSalesByItemOccupancyIn($row["occupancy_id"], array(18));
		$disc = getTotalRoomSalesByItemOccupancy(17, $row["occupancy_id"]);
		$ret .="<td>".$disc."</td>";



		$in_total = ($cost + $in_ot)-abs($disc) + $adjtot;

		$ret .="<td>".$in_total."</td>";
		$ret .="<td nowrap>".date("m/d g:i A", strtotime($row["actual_checkin"]))."</td>";
		$ret .="<td nowrap>".date("m/d g:i A", strtotime($row["actual_checkout"]))."</td>";


		$_sql = "select sum(a.unit_cost*a.qty)
			FROM room_sales a, occupancy_log b
			WHERE a.occupancy_id = b.occupancy_id
			AND a.item_id = '16'
			AND b.remarks in ('Overtime on checkout')
			and a.roomsales_id not in ('$inextid')
			AND a.occupancy_id = '".$row["occupancy_id"]."'";
		//echo $_sql.'<hr/>';
		$_res =mysql_query($_sql) or die(mysql_error());
		list($out_ot)=mysql_fetch_row($_res);
		$fnbtot	 = getTotalFoodSalesByItemOccupancyNotIn($row["occupancy_id"], array(17,21));
		$beertot = getTotalFoodSalesByItemOccupancyIn($row["occupancy_id"], array(17,21));
		$misctot = getTotalMiscSalesByCategoryOccupancyNotIn($row["occupancy_id"], array(2,3));
		
		$detot   = getTotalMiscSalesByItemOccupancyIn($row["occupancy_id"], array(27));
		

		$ot_total = $in_ot+$out_ot;
		if($ot_total=='0.00')$ot_total ='';		
		$total = ($in_total + $out_ot + $fnbtot + $misctot + $beertot ) - abs($detot);
		//$ret .="<td>". $row['occupancy_id']."</td>";	
		$ret .="<td>". $fnbtot."</td>";	
		$ret .="<td>".$beertot."</td>";	
		$ret .="<td>".$misctot."</td>";
		$ret .="<td>".$adjtot."</td>";	
		$ret .="<td>(".number_format(abs($detot),2).")</td>";	
		
		//$ret .="<td>".$ot_total."</td>"; //OT TOTAL
		$ret .="<td>".$out_ot."</td>"; //OT TOTAL
		$ret .="<td>".number_format($total,2)."</td>";	
		$ret .="</tr>";	
		$ftotal += $total;
		
		}
		$ret .="</table>";
		return $ret;
		
}



function getCheckoutSummary($start,$end,$suser_id,$euser_id,$lobbyid)
{
	$sql = "SELECT `room_type_id`, `room_type_name`, `site_id` FROM `room_types` order by rank";
	$res = mysql_query($sql);
	$ret .= "<div class='report'>";
	$ret .= "<b>CHECK OUT SUMMARY (Inclusive of food, misc, beer and others)</b><br>";	
	$ret.= "<b>SHIFT: </b>".getshift($start). "&nbsp;&nbsp;&nbsp;&nbsp;".date("m/d/Y - g:i:s A",strtotime($start));
	$ret.= "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>CASHIER: </b>";
	if($suser_id == $euser_id)
	{
		$_sql = "select fullname from users where user_id = '$euser_id'";
		$_res = mysql_query($_sql);
		list($cashier)=mysql_fetch_row($_res);
		$ret.=$cashier;
	}else
	{
		$_sql = "select fullname from users where user_id = '$suser_id'";
		$_res = mysql_query($_sql);
		list($scashier)=mysql_fetch_row($_res);
		$ret.=$scashier;

		$ret.=" - ";

		$_sql = "select fullname from users where user_id = '$euser_id'";
		$_res = mysql_query($_sql);
		list($ecashier)=mysql_fetch_row($_res);
		$ret.=$ecashier;
	}
	$ret.= "<br>";
	$ret.="</div>";	
	$ret .= "<table cellpadding=5 cellspacing=5 class='summary'>";
	$ret .= "<tr>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>#TOTAL</td>";
	$ret .= "<td>3 HRS</td>";
	$ret .= "<td>12 HRS</td>";
	$ret .= "<td>24 HRS</td>";
	$ret .= "<td>ROOM</td>";
	$ret .= "<td>OT</td>";
	$ret .= "<td>FOOD</td>";
	$ret .= "<td>BEER</td>";
	$ret .= "<td>MISC</td>";
	$ret .= "<td>ADJUST</td>";
	$ret .= "<td>DEDUC</td>";
	$ret .= "<td>TOTAL</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>Lobby</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>&nbsp;</td>";


	$fnblobbytot	= getLobbyFoodSalesByItemNotIn($lobbyid,$start,$end,array(17,21));
	$beerlobbytot	= getLobbyFoodSalesByItemIn($lobbyid,$start,$end,array(17,21));
	$misclobbytot	= getLobbyMiscSalesByItemNotIn($lobbyid,$start,$end,array(2,3));
	$lobbytotal		= $fnblobbytot + $beerlobbytot + $misclobbytot;


	if($fnblobbytot=='')$fnblobbytot='';
	else $fnblobbytot =number_format($fnblobbytot,2);

	if($beerlobbytot=='')$beerlobbytot='';
	else $beerlobbytot=number_format($beerlobbytot,2);
	
	if($misclobbytot=='')$misclobbytot='';
	else $misclobbytot=number_format($misclobbytot,2);
	
	if($lobbytotal=='0')$lobbytotal='0.00';
	else $lobbytotal=number_format($lobbytotal);

	$lobbytotal = $fnblobbytot + $beerlobbytot + $misclobbytot + $cooplobbytot;

	$ret .= "<td>$fnblobbytot</td>";
	$ret .= "<td>$beerlobbytot</td>";
	$ret .= "<td>$misclobbytot</td>";
	$ret .= "<td>&nbsp;</td>";	
	$ret .= "<td><b>". number_format($lobbytotal,2) ."</b></td>";	
	$ret .= "</tr>";

	$ret .= "<tr>";
	$ret .= "<td colspan=14>			
			<div style='border-width:3px;
			border-top-color:black;
			border-top-style:solid;
			border-right-style:hidden;
			border-bottom-style:hidden;
			border-left-style:hidden;
			text-align:left'>
			</div>			
			</td>";			
	$ret .= "</tr>";
	$site = "1";
	$ctr = 1;
	
	while(list($room_type_id,$room_type_name,$site_id) = mysql_fetch_row($res))
	{
		

		$ret .= "<tr>";
		$ret .= "<td>".$room_type_name."</td>
		<td>".getNumRoomByTypeID($room_type_id,$start,$end)."</td>";
			
		$_sql = "select rate_id from rates limit 0,3";
		$_res = mysql_query($_sql);
		while(list($rate_id)=mysql_fetch_row($_res))
		{
			$__sql = "select count(a.occupancy_id) from rates b , occupancy a, rooms c  
			where a.rate_id=b.rate_id
			and c.room_id = a.room_id
			and a.actual_checkout >= '$start' 
			and a.actual_checkout <= '$end'
			and b.rate_id = '$rate_id'
			and c.room_type_id = '$room_type_id'";
			$__res = mysql_query($__sql);
			list($cnt)=mysql_fetch_row($__res);
			if($cnt=='0')$cnt='&nbsp;';		
			$ret .= "<td>$cnt</td>";
			$rate_array[0]+=$cnt;
			$rate_array[$rate_id]+=$cnt;
		}

		//food
		$_sql = "select b.unit_cost*b.qty from occupancy a, fnb_sales b, rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkout >= '$start' 
			and a.actual_checkout <= '$end'
			and c.room_type_id = '$room_type_id'
			and (b.category_id <> '21' and b.category_id <> '17')";

		$_res = mysql_query($_sql) or die(mysql_error());
		$fnbtot ="";
		while(list($fnbamount)=mysql_fetch_row($_res))
		{
			$fnbtot = $fnbtot + $fnbamount;
		}

		//beer
		$_sql = "select b.unit_cost*b.qty from occupancy a, fnb_sales b, rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkout >= '$start' 
			and a.actual_checkout <= '$end'
			and c.room_type_id = '$room_type_id'
			and (b.category_id = '21' or b.category_id = '17')";
		$_res = mysql_query($_sql) or die(mysql_error());
		$beertot ="";
		while(list($beeramount)=mysql_fetch_row($_res))
		{
			$beertot = $beertot + $beeramount;
		}

		//misc
		$_sql = "select b.unit_cost*b.qty from occupancy a, room_sales b, rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkout >= '$start' 
			and a.actual_checkout <= '$end'
			and c.room_type_id = '$room_type_id'
			and b.category_id not in ('3','2')";
		
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		$_num = mysql_num_rows($_res);
		$misctotal = "";
		while(list($misccost)=mysql_fetch_row($_res))
		{			
			$misctotal = $misctotal + $misccost;				
		}

		//disc
		$_sql = "select unit_cost*qty from occupancy a, room_sales b,  rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkout >= '$start' 
			and a.actual_checkout <= '$end'
			and c.room_type_id = '$room_type_id'
			and b.item_id = '17'";
		if($site_id==2) {
			$_sql.=" AND b.sales_date >='".date('Y-m-d H:i:s',strtotime("$start - 1 day"))."'";
		}
		$_res = mysql_query($_sql);
		while(list($disc)=mysql_fetch_row($_res))
		{
			$disctotal += $disc;
		}
		
		//adjustment
		$_sql = "select unit_cost*qty from occupancy a, room_sales b,  rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkout >= '$start' 
			and a.actual_checkout <= '$end'
			and c.room_type_id = '$room_type_id'
			and b.item_id = '18'";
		$_res = mysql_query($_sql);

		while(list($adjamount)=mysql_fetch_row($_res))
		{
			$adjtot = $adjtot + $adjamount;
		}

		//deduction		
		$_sql = "select unit_cost*qty from occupancy a, room_sales b,  rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkout >= '$start' 
			and a.actual_checkout <= '$end'
			and c.room_type_id = '$room_type_id'
			and b.item_id = '27'";
		$_res = mysql_query($_sql);
		$detot = 0;
		while(list($deamount)=mysql_fetch_row($_res))
		{
			$detot = $detot + $deamount;
		}
		$detot = abs($detot);
		
		$roomtot = getRoomTotalByTypeID($room_type_id,$start,$end,$site_id)-abs($disctotal) + $adjtot;
		$disctotal = 0;
		$ottot = getOTByRoomTypeID($room_type_id,$start,$end,$site_id);
		$sitetotal += $roomtot + $ottot + $fnbtot + $beertot + $misctotal;
		$siteroomtotal += $roomtot;
		$siteottotal += $ottot;
		$sitefnbtotal += $fnbtot;
		$sitebeertotal += $beertot;
		$sitemisctotal += $misctotal;
		$sitecooptotal += $cooptotal;
		$siteadjtot += $adjtot;
		$sitedetot += $detot;
		$grandtotal = ($roomtot + $ottot + $fnbtot + $beertot + $misctotal + $adjtot) - ($detot);
		
		if($roomtot>0)$froomtot=number_format($roomtot);else $froomtot='';
		if($ottot>0)$fottot=number_format($ottot);else $fottot='';
		if($fnbtot>0)$ffnbtot=number_format($fnbtot);else $ffnbtot='';
		if($beertot>0)$fbeertot=number_format($beertot);else $fbeertot='';
		if($misctotal>0)$fmisctotal=number_format($misctotal);else $fmisctotal='';
		if($cooptotal>0)$fcooptotal=number_format($cooptotal);else $fcooptotal='';
		if($adjtot>0)$fadjtot=number_format($adjtot);else $fadjtot='';
		if($detot>0)$fdetot=number_format($detot);else $fdetot='';
		
		$ret .= "<td>".$froomtot."</td>";
		$ret.= "<td>".$fottot."</td>";
		$ret .= "<td>".$ffnbtot."</td>";
		$ret .= "<td>".$fbeertot."</td>";
		$ret .= "<td>".$fmisctotal."</td>";	
		$ret .= "<td>".$fadjtot."</td>";
		$ret .= "<td>".$fdetot."</td>";	
				

		$ret.= "<td>".number_format($grandtotal)."</td>";		
		$ret .= "</tr>";

		 
		$i++;

		if($ctr == 6)
		{			
			$ret .= "<tr>";
			$ret .= "<td colspan=14>			
			<div style='border-width:3px;
			border-top-color:black;
			border-top-style:solid;
			border-right-style:hidden;
			border-bottom-style:hidden;
			border-left-style:hidden;
			text-align:left'>
			
			</div>			
			</td>";			
			$ret .= "</tr>";
			$ret .= "<tr>";
			$sql1 = "select site_name from sites where site_id = '$site_id'";
			$res1 = mysql_query($sql1);
			list($sitename)=mysql_fetch_row($res1);
			$ret .= "<td colspan='1'><b>$sitename TOTAL</b></td>";
			foreach($rate_array as $rate=>$count) {
				$ret.="<td>$count</td>";
			}
			$ret .= "<td>".number_format($siteroomtotal)."</td>";
			$ret .= "<td>".number_format($siteottotal)."</td>";
			$ret .= "<td>".number_format($sitefnbtotal)."</td>";
			$ret .= "<td>".number_format($sitebeertotal)."</td>";
			$ret .= "<td>".number_format($sitemisctotal)."</td>";
			//$ret .= "<td>".number_format($sitecooptotal)."</td>";
			$ret .= "<td>".number_format($siteadjtot)."</td>";
			$ret .= "<td>".number_format($sitedetot)."</td>";
			$ret .= "<td><b>".number_format($sitetotal)."</b></td>";
			$ret .= "</tr>";
			
			$gtotal += $sitetotal;
			$sitetotal = 0;
			$siteroomtotal = 0;
			$siteottotal = 0;
			$sitefnbtotal = 0;
			$sitebeertotal = 0;
			$sitemisctotal = 0;
			$sitecooptotal = 0;
			$siteadjtot = 0;
			$sitedetot = 0;

			$site += 1;
			
		}
		
		$ctr = $ctr + 1;
		
		
	}
	$ret .= "<tr>";
		$ret .= "<td colspan=11>			
			<div style='text-align:right'>
			<br><br><br>
			<b>GRAND TOTAL: ".number_format($gtotal+$lobbytotal)."<b>
			</div>
			</td>";	
		$ret .= "</tr>";
	$ret .= "</table>";
	
	return $ret;
}

function getRoomTotalByTypeID($room_type_id,$startshift,$end,$siteid=1)
{
	$sql = "select a.occupancy_id,a.rate_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout >= '".$startshift."'
	and a.actual_checkout <= '".$end."'
	and b.room_type_id = '$room_type_id'";
	
	$res = mysql_query($sql);
	$total = 0;
	$origstart = $start;
	while(list($id,$rateid)=mysql_fetch_row($res))
	{
		//-->
		$start = $origstart;
		$ratehours = 0;
		$weightedstart=0;
		if($rateid == 2) {
			$ratehours=12;
		}elseif($rateid == 3) {
			$ratehours=24;
		}

		if($ratehours) {
			$weightedstart = date('Y-m-d H:i:s', strtotime("$end - $ratehours hours"));
		}
		if($weightedstart) $start = $weightedstart;
		//-->


		$_sql = "select unit_cost*qty from room_sales where item_id = '15' and occupancy_id = '$id'";
		
		//if($weightedstart) $_sql.=" and sales_date > '$start' and sales_date < '$end' ";

		$_sql .= " order by roomsales_id desc limit 1";
		//echo "$_sql <hr>";
	
		$_res = mysql_query($_sql);
		list($unitqty)=mysql_fetch_row($_res);
		$total += $unitqty;
	}

	return $total;
	
}

function getNumRoomByTypeID($room_type_id,$startshift,$end)
{
	$sql = "select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout >= '".$startshift."'
	and a.actual_checkout <= '".$end."'
	and b.room_type_id = '$room_type_id'";
	$res = mysql_query($sql);
	$num = mysql_num_rows($res);
	return $num;
}

function getOTByRoomTypeID($roomtypeid,$startdt,$end,$siteid)
{
	
	$sql = "select a.occupancy_id,a.rate_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout >= '".$startdt."'
	and a.actual_checkout <= '".$end."'
	and b.room_type_id = '$roomtypeid'";

	$res = mysql_query($sql) or die(mysql_error().$sql);
	
	$origstart=$startdt;
	while(list($occupancy_id,$rateid)=mysql_fetch_row($res))
	{		
		//-->
		$start = $origstart;
		$ratehours = 0;
		$weightedstart=0;
		if($rateid == 2) {
			$ratehours=12;
		}elseif($rateid == 3) {
			$ratehours=24;
		}

		if($ratehours) {
			$weightedstart = date('Y-m-d H:i:s', strtotime("$end - $ratehours hours"));
		}
		if($weightedstart) $start = $weightedstart;
		//-->

		$_sql = "
		select a.unit_cost,a.qty from room_sales a, occupancy_log  b
		where a.item_id ='16'
		and a.occupancy_id=b.occupancy_id
		and b.transaction_date=a.update_date
		and a.occupancy_id in ($occupancy_id) 
		and a.status in ('Paid')";
		
		if($weightedstart) $_sql.=" and sales_date > '$start' and sales_date < '$end' ";

		//echo "$_sql<hr>";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$num = $unit_cost * $qty;
			if($num > 0)
			{
				$total = $total + ($unit_cost * $qty);
			}	
		}
	}
	
//echo $sql . "<hr />";	
	return $total;
}

function getLatestShifts($shiftid)
{
	$sql = "SELECT `shift-transaction_id`,datetime,user_id  FROM `shift-transactions` where shift = 'start' order by datetime desc ";
	$res = mysql_query($sql);
	$ret = "<select name='rblshifts' id='rblshifts' onchange='myform.submit();'>";
	$ret .= "<option value=''>&nbsp;</option>";
	while(list($shift_transaction_id,$datetime,$userid)=mysql_fetch_row($res))
	{
		if($shiftid == $shift_transaction_id)
		{
			$select = "selected";
		}else
		{
			$select = " ";
		}
		$__sql = "SELECT user_id  FROM `shift-transactions` where shift = 'end' 
		and `datetime` > '$datetime'
		order by datetime asc 
		limit 0,1";
		$__res = mysql_query($__sql);
		list($userid)=mysql_fetch_row($__res);
		$_sql = "select fullname from users where user_id = '$userid'";
		$_res = mysql_query($_sql);
		list($username)=mysql_fetch_row($_res);
		$ret .= "<option value='$shift_transaction_id' $select>$datetime - $username - ".getshift($datetime)."</option>";
	}
	$ret .= "</select>";

	return $ret;
}


$shiftid = $_POST["rblshifts"];
$sql = "select datetime,user_id from `shift-transactions` where `shift-transaction_id` = '$shiftid'";
$res = mysql_query($sql);
list($sdatetime,$suser_id)=mysql_fetch_row($res);
$startshift = $sdatetime;
$sql = "select datetime,user_id from `shift-transactions` where datetime  > '$startshift' order by datetime asc limit 0,1";
$res = mysql_query($sql);
list($edatetime,$euser_id)=mysql_fetch_row($res);
$end = $edatetime;

if(!$end)
{
	$end = date("Y-m-d H:i:s");
}


function getshift($date) {

	if(!$date)$date=date("Y-m-d H:i:s");
	list($d, $t) = explode(" ", $date);
	list($h, $m, $s) = explode(":", $t);
	$sql = "select shift_id from shifts where $h between shift_start and shift_end";
	$res = mysql_query($sql) or die(mysql_error() .$sql);
	list($shift)=mysql_fetch_row($res);
	
	if($h==16||$h==15)
	{
		return $shift = "3rd";
	}
	elseif($h==8||$h==7)
	{
		return $shift = "2nd";
	}
	return  "1st";
}

/////////////////////////////////////////////////////////////////////
function getRechitDetailReport($start,$end,$suser_id,$euser_id,$lobbyid)
{
	$sql = "select settings_value from settings where id = '1'";
	$res = mysql_query($sql);
	list($value) = mysql_fetch_row($res);
	$ret.="<div class='report'>";
	$ret.= "<b>".strtoupper($value)."</b><br>";
	$ret.= "<b>RECHIT DETAIL REPORT</b><br>";
	$ret.= "<b>SHIFT: </b>".getshift($start). "&nbsp;&nbsp;&nbsp;&nbsp;".date("m/d/Y g:i:s A",strtotime($start));
	$ret.= "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>CASHIER: </b>";
	if($suser_id == $euser_id)
	{
		$_sql = "select fullname from users where user_id = '$euser_id'";
		$_res = mysql_query($_sql);
		list($cashier)=mysql_fetch_row($_res);
		$ret.=$cashier;
	}else
	{
		$_sql = "select fullname from users where user_id = '$suser_id'";
		$_res = mysql_query($_sql);
		list($scashier)=mysql_fetch_row($_res);
		$ret.=$scashier;

		$ret.=" - ";

		$_sql = "select fullname from users where user_id = '$euser_id'";
		$_res = mysql_query($_sql);
		list($ecashier)=mysql_fetch_row($_res);
		$ret.=$ecashier;
	}
	
	
	$ret.= "<br>";
	$ret.="</div>";
	$ret.= "<br>";
	$ret.= "<br>";
	$ret.= "<br>";
	$hr = date('H',strtotime($start));
	if($hr >= 7 && $hr < 14) {
		$thisshift=2;
	}elseif($hr >=15 && $hr < 24){
		$thisshift=3;
	}else{
		$thisshift=1;
	}
	
	
	$sql = "
	create temporary table temprechitdetails 
	select a.occupancy_id,a.room_id,a.actual_checkin,a.actual_checkout,a.rate_id,b.room_type_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.shift_checkin='$thisshift'
	and a.actual_checkin < '$start' and a.actual_checkout='0000-00-00 00:00:00'
	and b.site_id = '2'
	and a.occupancy_id<>'$lobbyid'
	union
	select a.occupancy_id,a.room_id,a.actual_checkin,a.actual_checkout,a.rate_id,b.room_type_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.shift_checkin='$thisshift'
	and a.actual_checkin < '$start' and a.actual_checkout>'$end'
	and b.site_id = '2'
	and a.occupancy_id<>'$lobbyid'
	";
	mysql_query($sql);
	$sql="select a.* from temprechitdetails a, room_types b 
		where a.room_type_id=b.room_type_id
		order by b.rank
	";

	/*
	* get date range  for transactions that encompass the 12h or 24h stay
	*/
	

	//echo $sql;

	$res = mysql_query($sql) or die($sql);

	$ret.= "<table cellpadding=4 class='report' >";
	$ret.= "<tr>";
	$ret.= "<th>RM_TYPE</th>";
	$ret.= "<th>RM_NO</th>";
	$ret.= "<th>HRS</th>";
	$ret.= "<th>RATE</th>";
	$ret.= "<th>OT</th>";	
	$ret.= "<th>DISC</th>";
	$ret.= "<th>INTOT</th>";
	$ret.= "<th>CHECKIN</th>";
	$ret.= "<th>CHECKOUT</th>";
	$ret.= "<th>FOOD</th>";
	$ret.= "<th>BEER</th>";
	$ret.= "<th>MISC</th>";
	$ret.= "<th>ADJUST</th>";
	$ret.= "<th>DEDUCT</th>";
	$ret.= "<th>OT</th>";
	$ret.= "<th>TOTAL</th>";
	$ret.= "</tr>";
	$origstart = $start;
	while($row = mysql_fetch_array($res))
	{
		//-->
		$start = $origstart;
		$ratehours = 0;
		$weightedstart=0;
		if($row["rate_id"] == 2) {
			$ratehours=12;
		}elseif($row["rate_id"] == 3) {
			$ratehours=24;
		}

		if($ratehours) {
			$weightedstart = date('Y-m-d H:i:s', strtotime("$end - $ratehours hours"));
		}

		if($weightedstart) $start = $weightedstart;
		//-->
		
		//need to also get site_id to determine where rechit is applied
		$_sql = "select room_type_id,door_name, site_id from rooms where room_id = '".$row["room_id"]."'";
		$_res = mysql_query($_sql) or die($_sql);
		list($id,$door_name, $site_id)=mysql_fetch_row($_res);
		$_sql = "select room_type_name from room_types where room_type_id = '$id'";
		$_res = mysql_query($_sql);
		list($room_type_name)=mysql_fetch_row($_res);
		


		$ret .="<tr>";
		$ret .="<td>".$room_type_name."</td>";
		$ret .="<td>".$door_name."</td>";

		$_sql = "select rate_name from rates where rate_id = '".$row["rate_id"]."'";
		$_res = mysql_query($_sql);
		list($rate_name)=mysql_fetch_row($_res);
		$rate_name = str_replace("HRS","",$rate_name);
		$ret .="<td>".$rate_name."</td>";

		$_sql = "select unit_cost*qty from room_sales where occupancy_id = '".$row["occupancy_id"]."' 
				and status in ('Paid')
				and item_id in (15,16)
				and sales_date >='$start'
				and sales_date < '$end'
				order by roomsales_id desc
				limit 0,1";
				
		//echo "$_sql<hr>";		
		$_res = mysql_query($_sql);
		list($cost)=mysql_fetch_row($_res);
		
		$ret .="<td>".$cost."</td>";

		//apply rechit here
		$_sql = "select a.unit_cost*a.qty, a.roomsales_id
			FROM room_sales a, occupancy_log b
			WHERE a.occupancy_id = b.occupancy_id
			AND b.transaction_date = a.update_date
			AND a.item_id = '16'
			AND b.remarks not in ('Overtime on checkout')
			AND a.status in ('Paid')
			AND a.occupancy_id = '".$row["occupancy_id"]."'";
		$_sql.=" AND a.sales_date >='$start' and a.sales_date < '$end' ";

		$_res = mysql_query($_sql) or die($_sql);

		list($in_ot,$inextid)=mysql_fetch_row($_res);

		if($in_ot=='')
		{
			$din_ot = '';	
		}else{
			$din_ot=$in_ot;
		}

		$ret .="<td>".$din_ot."</td>";

		//disc
		$_sql = "select unit_cost*qty from room_sales 
			where status in ('Paid')
			and item_id = '17'
			and occupancy_id = '".$row["occupancy_id"]."'";
		$_sql.=" AND sales_date >='$start' and sales_date < '$end' ";
		
		//adjustment
		$_sql = "select unit_cost*qty from room_sales 
			where status in ('Paid')
			and item_id = '18'
			and occupancy_id = '".$row["occupancy_id"]."'";
		$_sql.=" AND sales_date >='$start' and sales_date < '$end' ";
		$_res = mysql_query($_sql);
		$adjtot = 0;
		while(list($adjamount)=mysql_fetch_row($_res))
		{
			$adjtot = $adjtot + $adjamount;
		}

		//deduction
		$_sql = "select unit_cost*qty from room_sales 
			where status in ('Paid')
			and item_id = '27'
			and occupancy_id = '".$row["occupancy_id"]."'";
			$_sql.=" AND sales_date >='$start' and sales_date < '$end' ";

		$_res = mysql_query($_sql);
		$detot = 0;
		while(list($deamount)=mysql_fetch_row($_res))
		{
			$detot = $detot + $deamount;
		}

		
		
		$_res = mysql_query($_sql);
		list($disc)=mysql_fetch_row($_res);
		if(!$disc){
			$disc ='';
		}else{
			$disc=abs($disc);
		}
		$ret .="<td>".$disc."</td>";
		

		$in_total = ($cost+$in_ot)-$disc;
		

		$ret .="<td>".$in_total."</td>";
$ret .="<td>".$row['occupancy_id']."</td>";
		

		$ret .="<td nowrap>".date("m/d g:i A", strtotime($row["actual_checkin"]))."</td>";

		if($row["actual_checkout"]=='0000-00-00 00:00:00') {
			$begin = new DateTime($row['actual_checkin']); // 31 days
			$time_span = $begin->diff(new DateTime(date('Y-m-d H:i:s')));
			$ret .="<td nowrap>Day {$time_span->d}</td>";
		} else {
			$ret .="<td nowrap>".date("m/d g:i A", strtotime($row["actual_checkout"]))."</td>";
		}

		
		$_sql = "select unit_cost*qty
			FROM room_sales
			WHERE item_id = '16'
			AND roomsales_id not in ('$inextid') 
			AND occupancy_id = '".$row["occupancy_id"]."'";
		$_sql.="  AND sales_date >='$start' and sales_date < '$end' ";
		
		
		//echo $_sql; 
		$_res = mysql_query($_sql);
		
		$out_ot1 = 0;
		list($ecost)=mysql_fetch_row($_res);
		/*
		while(list($ecost)=mysql_fetch_row($_res))
		{
			$out_ot1 += $ecost;
		}
		*/

		//food
		$_sql = "select unit_cost*qty from fnb_sales 
			where status in ('Paid')
			and category_id not in( 21,17 )
			and occupancy_id = '".$row["occupancy_id"]."'";
		$_sql.=" AND sales_date >'$start' and sales_date < '$end' ";
		
		$_res = mysql_query($_sql) or die(mysql_error());
		$fnbtot =0;
		while(list($fnbamount)=mysql_fetch_row($_res))
		{
			$fnbtot = $fnbtot + $fnbamount;
		}
		
		//beer 
		$_sql = "select unit_cost*qty from fnb_sales 
			where status in ('Paid')
			and category_id in (17,21)
			and occupancy_id = '".$row["occupancy_id"]."'";
		$_sql.="  AND sales_date >='$start' and sales_date < '$end' ";

		$_res = mysql_query($_sql);
		$beertot = 0;
		while(list($beeramount)=mysql_fetch_row($_res))
		{
			$beertot = $beertot + $beeramount;
		}

		//misc
		$_sql = "select unit_cost*qty from room_sales 
			where status in ('Paid')
			and category_id not in ('3','2')
			and occupancy_id = '".$row["occupancy_id"]."'";
		if($site_id==2) {
			$_sql.="  AND sales_date >='$start' and sales_date < '$end' ";
		}	
		$_res = mysql_query($_sql);
		$misctot = 0;
		while(list($miscamount)=mysql_fetch_row($_res))
		{
			$misctot = $misctot + $miscamount;
		}


		
		
		
		$ot_total = $in_ot+$out_ot1;
		if($ot_total=='0.00')$ot_total ='';		
		$total = ($in_total + $out_ot1 + $fnbtot + $misctot + $beertot + $adjtot) ;
		if($fnbtot=='0.00')$fnbtot ='';
		else $fnbtot =number_format($fnbtot,2);
		if($beertot=='0.00')$beertot ='';
		else $beertot =number_format($beertot,2);
		if($misctot=='0.00')$misctot ='';
		else $misctot =number_format($misctot,2);
		
		if($adjtot=='0.00')$adjtot ='';
		else $adjtot =number_format($adjtot,2);
		$ret .="<td>".$fnbtot."</td>";	
		$ret .="<td>".$beertot."</td>";	
		$ret .="<td>".$misctot."</td>";
		$ret .="<td>".$adjtot."</td>";	
		$ret .="<td>(".number_format(abs($detot),2).")</td>";	
		
		$ret .="<td>".$ot_total."</td>"; //OT TOTAL
		$ret .="<td>".number_format($total,2)."</td>";	
		$ret .="</tr>";	
		$ftotal += $total;
		
		}
		$ret .="</table>";

		return $ret;
		
}


function getRechitNumRoomByTypeID($room_type_id,$startshift,$end,$lobbyid)
{
	$hr = date('H',strtotime($startshift));
	if($hr >= 7 && $hr < 14) {
		$thisshift=2;
	}elseif($hr >=15 && $hr < 24){
		$thisshift=3;
	}else{
		$thisshift=1;
	}
	
	$checkincutoff = date('Y-m-d H:i:s', strtotime("$startshift -1 day"));
	$sql = "
	select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.shift_checkin='$thisshift'
	and a.actual_checkin < '$startshift' and a.actual_checkout='0000-00-00 00:00:00'
	and b.room_type_id = '$room_type_id'
	and a.occupancy_id<>'$lobbyid'
	union
	select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.shift_checkin='$thisshift'
	and a.actual_checkin < '$startshift' and a.actual_checkout>'$end'
	and b.room_type_id = '$room_type_id'
	and a.occupancy_id<>'$lobbyid'
	";

	$res = mysql_query($sql);
	$num = mysql_num_rows($res);
	
	return $num;
}

function getRechitRoomTotalByTypeID($room_type_id,$startshift,$end,$siteid=1)
{
	$checkincutoff = date('Y-m-d H:i:s', strtotime("$startshift -1 day"));
	$hr = date('H',strtotime($startshift));
	if($hr >= 7 && $hr < 14) {
		$thisshift=2;
	}elseif($hr >=15 && $hr < 24){
		$thisshift=3;
	}else{
		$thisshift=1;
	}

	$sql = "select a.occupancy_id, a.shift_checkin from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.shift_checkin='$thisshift'
	and a.actual_checkin < '$startshift' and a.actual_checkout='0000-00-00 00:00:00'
	and b.room_type_id = '$room_type_id'
	and a.occupancy_id<>'$lobbyid'
	union
	select a.occupancy_id, a.shift_checkin from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.shift_checkin='$thisshift'
	and a.actual_checkin < '$startshift' and a.actual_checkout>'$end'
	and b.room_type_id = '$room_type_id'
	and a.occupancy_id<>'$lobbyid'";
	
	
	$res = mysql_query($sql) or die($sql . mysql_error() ."<hr/>");

	$total = 0;
	while(list($id,$shift)=mysql_fetch_row($res))
	{
		
		if($thisshift==$shift) {
			$_sql = "select unit_cost*qty from room_sales where item_id = '15' and occupancy_id = '$id' order by roomsales_id limit 1";

			$_res = mysql_query($_sql);
				
			list($unitqty)=mysql_fetch_row($_res);
			$total += $unitqty;
		}
	}

	return $total;
	
}

function getRechitOTByRoomTypeID($roomtypeid,$startdt,$end,$siteid,$lobbyid)
{
	$checkincutoff = date('Y-m-d H:i:s', strtotime("$startdt -1 day"));
	$hr = date('H',strtotime($startdt));
	if($hr >= 7 && $hr < 14) {
		$thisshift=2;
	}elseif($hr >=15 && $hr < 24){
		$thisshift=3;
	}else{
		$thisshift=1;
	}
	$sql = "select a.occupancy_id, a.shift_checkin from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.shift_checkin='$thisshift'
	and a.actual_checkin < '$startshift' and a.actual_checkout='0000-00-00 00:00:00'
	and b.room_type_id = '$room_type_id'
	and a.occupancy_id<>'$lobbyid'
	union
	select a.occupancy_id, a.shift_checkin from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.shift_checkin='$thisshift'
	and a.actual_checkin < '$startshift' and a.actual_checkout>'$end'
	and b.room_type_id = '$room_type_id'
	and a.occupancy_id<>'$lobbyid'";
	
	$res = mysql_query($sql) or die(mysql_error().$sql);
	
	while(list($occupancy_id,$shift)=mysql_fetch_row($res))
	{		
		if($thisshift==$shift && $occupancy_id!='') {
			$_sql = "select unit_cost,qty from room_sales 
			where item_id ='16' 
			and occupancy_id in ($occupancy_id) 
			and status in ('Paid')
			";

			$_res = mysql_query($_sql) or die(mysql_error().$_sql);
			while(list($unit_cost,$qty)=mysql_fetch_row($_res))
			{
				$num = $unit_cost * $qty;
				if($num > 0)
				{
					$total = $total + ($unit_cost * $qty);
				}	
			}
		}		
	}
	
	return $total;
}

function getRechitSummary($start,$end,$suser_id,$euser_id,$lobbyid) {
	$checkincutoff = date('Y-m-d H:i:s', strtotime("$start -1 day"));
	$hr = date('H',strtotime($start));
	if($hr >= 7 && $hr < 14) {
		$thisshift=2;
	}elseif($hr >=15 && $hr < 24){
		$thisshift=3;
	}else{
		$thisshift=1;
	}
$sql = "SELECT `room_type_id`, `room_type_name`, `site_id` FROM `room_types` where site_id=2 order by rank";
	$res = mysql_query($sql);
	$ret .= "<b>RECHIT SUMMARY (Inclusive of food, misc, beer and others)</b><br>";
	$ret .= "<table cellpadding=5 cellspacing=5 class='summary'>";
	$ret .= "<tr>";
	$ret .= "<td>&nbsp;</td>";
	$ret .= "<td>#TOTAL</td>";
	$ret .= "<td>3 HRS</td>";
	$ret .= "<td>12 HRS</td>";
	$ret .= "<td>24 HRS</td>";
	$ret .= "<td>ROOM</td>";
	$ret .= "<td>OT</td>";
	$ret .= "<td>FOOD</td>";
	$ret .= "<td>BEER</td>";
	$ret .= "<td>MISC</td>";
	$ret .= "<td>ADJUST</td>";
	$ret .= "<td>DEDUC</td>";
	$ret .= "<td>TOTAL</td>";
	
	$ret .= "</tr>";

	$site = "1";

	while(list($room_type_id,$room_type_name,$site_id) = mysql_fetch_row($res))
	{
		$ret .= "<td>".$room_type_name."</td>
		<td>".getRechitNumRoomByTypeID($room_type_id,$start,$end,$lobbyid)."</td>";
		
		$_sql = "select rate_id from rates limit 0,3";
		$_res = mysql_query($_sql);
		while(list($rate_id)=mysql_fetch_row($_res))
		{
			if($rate_id==2) {
				$checkincutoff = date('Y-m-d H:i:s', strtotime("$end -12 hour"));
			}elseif($rate_id==3){
				$checkincutoff = date('Y-m-d H:i:s', strtotime("$end -24 hour"));
			}else{
				$checkincutoff = $start;
			}
			$__sql = "select a.occupancy_id, a.shift_checkin from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.shift_checkin=$thisshift
	and a.actual_checkin < '$start' and a.actual_checkout='0000-00-00 00:00:00'
	and b.room_type_id = '$room_type_id'
	and a.occupancy_id<>'$lobbyid'
	and a.rate_id='$rate_id'
	union
	select a.occupancy_id, a.shift_checkin from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.shift_checkin=$thisshift
	and a.actual_checkin < '$start' and a.actual_checkout>'$end'
	and b.room_type_id = '$room_type_id'
	and a.rate_id='$rate_id'
	and a.occupancy_id<>'$lobbyid'";

			$__res = mysql_query($__sql);
			$cnt=mysql_num_rows($__res);
			if($cnt=='0')$cnt='&nbsp;';		
			$ret .= "<td>$cnt</td>";
			$rate_array[0]+=$cnt;
			$rate_array[$rate_id]+=$cnt;
		}

		//food
		$_sql = "select b.unit_cost*b.qty,a.occupancy_id,a.actual_checkin,a.actual_checkout,b.item_id,c.room_type_id from occupancy a, fnb_sales b, rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.actual_checkin < '$start'
			and a.shift_checkin=$thisshift
			and a.actual_checkout='0000-00-00 00:00:00' 
			and a.room_id = c.room_id	
			and c.room_type_id = '$room_type_id'
			and a.occupancy_id<>'$lobbyid'
			and b.category_id not in(21,17)
			 AND b.sales_date >='$checkincutoff' and b.sales_date <'$end' 
		union
		select b.unit_cost*b.qty,a.occupancy_id,a.actual_checkin,a.actual_checkout,b.item_id,c.room_type_id from occupancy a, fnb_sales b, rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.actual_checkin < '$start'
			and a.actual_checkout >'$end' 
			and a.room_id = c.room_id	
			and a.shift_checkin=$thisshift
			and c.room_type_id = '$room_type_id'
			and a.occupancy_id<>'$lobbyid'
			and b.category_id not in(21,17)
			 AND b.sales_date >='$checkincutoff' and b.sales_date <'$end' 	 
		";

		$_res = mysql_query($_sql) or die(mysql_error());
		$fnbtot ="";
		while(list($fnbamount)=mysql_fetch_row($_res))
		{
			$fnbtot = $fnbtot + $fnbamount;
		}

		//beer
		$_sql = "select b.unit_cost*b.qty from occupancy a, fnb_sales b, rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkin < '$start'
			and (a.actual_checkout='0000-00-00 00:00:00' or a.actual_checkout > '$end')
			and a.occupancy_id<>'$lobbyid'
			and c.room_type_id = '$room_type_id'
			and (b.category_id = '21' or b.category_id = '17')";
		$_sql.=" AND b.sales_date >='$checkincutoff' and b.sales_date <'$end' ";
		$_res = mysql_query($_sql) or die(mysql_error());
		$beertot ="";
		while(list($beeramount)=mysql_fetch_row($_res))
		{
			$beertot = $beertot + $beeramount;
		}

		//misc
		$_sql = "select b.unit_cost*b.qty from occupancy a, room_sales b, rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkin <= '$start'
			and a.shift_checkin='$thisshift'			
			and (a.actual_checkout > '$end' or a.actual_checkout='0000-00-00 00:00:00')
			and c.room_type_id = '$room_type_id'
			and a.occupancy_id<>'$lobbyid'
			and b.category_id not in ('3','2')";
		$_sql.=" AND b.sales_date >='$checkincutoff' and b.sales_date <'$end' ";
		
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		$_num = mysql_num_rows($_res);
		$misctotal = "";
		while(list($misccost)=mysql_fetch_row($_res))
		{			
			$misctotal = $misctotal + $misccost;				
		}

		//disc
		$_sql = "select unit_cost*qty from occupancy a, room_sales b,  rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.shift_checkin=$thisshift
			and a.actual_checkin <= '$start' 
			and (a.actual_checkout > '$end' or a.actual_checkout='0000-00-00 00:00:00')
			and a.occupancy_id<>'$lobbyid'
			and c.room_type_id = '$room_type_id'
			and b.item_id = '17'";
		$_sql.=" AND b.sales_date >='$checkincutoff' and b.sales_date <'$end' ";
		//echo "$_sql<hr>";
		
		$_res = mysql_query($_sql);
		while(list($disc)=mysql_fetch_row($_res))
		{
			$disctotal += $disc;
		}
		
		//adjustment
		$_sql = "select unit_cost*qty from occupancy a, room_sales b,  rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkin <= '$start' 
			and (a.actual_checkout > '$end' or a.actual_checkout='0000-00-00 00:00:00')
			and a.occupancy_id<>'$lobbyid'
			and c.room_type_id = '$room_type_id'
			and b.item_id = '18'";
		$_sql.=" AND b.sales_date >='$checkincutoff' and b.sales_date <'$end' ";
		$_res = mysql_query($_sql);
		$adjtot = 0;
		while(list($adjamount)=mysql_fetch_row($_res))
		{
			$adjtot = $adjtot + $adjamount;
		}

		//deduction		
		$_sql = "select unit_cost*qty from occupancy a, room_sales b,  rooms c
			where b.status in ('Paid')
			and a.occupancy_id = b.occupancy_id	
			and a.room_id = c.room_id	
			and a.actual_checkin <= '$start' 
			and (a.actual_checkout > '$end' or a.actual_checkout='0000-00-00 00:00:00')
			and a.occupancy_id<>'$lobbyid'
			and c.room_type_id = '$room_type_id'
			and b.item_id = '27'";
		$_sql.=" AND b.sales_date >='$checkincutoff' and b.sales_date <'$end' ";
		//echo $_sql.'<hr />';
		$_res = mysql_query($_sql);
		$detot = 0;
		while(list($deamount)=mysql_fetch_row($_res))
		{
			$detot = $detot + $deamount;
		}
		
		
		$roomtot = getRechitRoomTotalByTypeID($room_type_id,$start,$end,$site_id)-abs($disctotal)+$detot;
		$disctotal = 0;
		$ottot = getRechitOTByRoomTypeID($room_type_id,$start,$end,$site_id,$lobbyid);
		
		$sitetotal += $roomtot + $ottot + $fnbtot + $beertot + $misctotal + $adjtot + $detot;
		$siteroomtotal += $roomtot;
		$siteottotal += $ottot;
		$sitefnbtotal += $fnbtot;
		$sitebeertotal += $beertot;
		$sitemisctotal += $misctotal;
		$siteadjtotal += $adjtot;
		$sitedetotal += $detot;
		$grandtotal = $roomtot + $ottot + $fnbtot + $beertot + $misctotal+$adjtot;
		if($roomtot>0)$froomtot=number_format($roomtot);else $froomtot='';
		if($ottot>0)$fottot=number_format($ottot);else $fottot='';
		if($fnbtot>0)$ffnbtot=number_format($fnbtot);else $ffnbtot='';
		if($beertot>0)$fbeertot=number_format($beertot);else $fbeertot='';
		if($misctotal>0)$fmisctotal=number_format($misctotal);else $fmisctotal='';
		if($adjtot<>0)$fadjtotal=number_format($adjtot);else $fadjtotal='';
		if($detot<>0)$fdetotal=number_format($detot);else $fdetotal='';
		$ret .= "<td>".$froomtot."</td>";
		$ret.= "<td>".$fottot."</td>";
		$ret .= "<td>".$ffnbtot."</td>";
		$ret .= "<td>".$fbeertot."</td>";
		$ret .= "<td>".$fmisctotal."</td>";		
$ret .= "<td>".$fadjtotal."</td>";
$ret .= "<td>".$fdetotal."</td>";
		$ret.= "<td>".number_format($grandtotal)."</td>";		
		$ret .= "</tr>";

		 
		$i++;


		
		
	}
	
	$ret .= "<tr>";
			$ret .= "<td colspan=14>			
			<div style='border-width:3px;
			border-top-color:black;
			border-top-style:solid;
			border-right-style:hidden;
			border-bottom-style:hidden;
			border-left-style:hidden;
			text-align:left'>
			</div>			
			</td>";			
			$ret .= "</tr>";
			$ret .= "<tr>";
			$sql1 = "select site_name from sites where site_id = '$site_id'";
			$res1 = mysql_query($sql1);
			list($sitename)=mysql_fetch_row($res1);
			$ret .= "<td colspan='1'><b>$sitename TOTAL</b></td>";
			foreach($rate_array as $rate=>$count) {
				$ret.="<td>$count</td>";
			}		
					
			$ret .= "<td>".number_format($siteroomtotal)."</td>";
			$ret .= "<td>".number_format($siteottotal)."</td>";
			$ret .= "<td>".number_format($sitefnbtotal)."</td>";
			$ret .= "<td>".number_format($sitebeertotal)."</td>";
			$ret .= "<td>".number_format($sitemisctotal)."</td>";
$ret .= "<td>".number_format($siteadjtotal)."</td>";
$ret .= "<td>".number_format($sitedetotal)."</td>";
			$ret .= "<td><b>".number_format($sitetotal)."</b></td>";
			$ret .= "</tr>";
			
			$gtotal += $sitetotal;
			$sitetotal = 0;
			$siteroomtotal = 0;
			$siteottotal = 0;
			$sitefnbtotal = 0;
			$sitebeertotal = 0;
			$sitemisctotal = 0;
			$sitecooptotal = 0;
	$ret .= "<tr>";
		$ret .= "<td colspan=11>			
			<div style='text-align:right'>
			<br><br><br>
			<b>GRAND TOTAL: ".number_format($gtotal)."<b>
			</div>
			</td>";	
		$ret .= "</tr>";
	$ret .= "</table>";

	return $ret;

}

function getCoopSummary($startshift,$end,$suser_id,$euser_id,$lobbyid) {
	include_once('acctg/class.baseobject.php');
	include_once('acctg/class.report.php');
	$sql= "
	select room_types.room_type_name as 'RM_TYPE', rooms.door_name as 'RM_NO', room_sales.sales_date as 'SALES DATE', 
	occupancy.actual_checkin as 'CHECKIN', occupancy.actual_checkout as 'CHECKOUT', sales_and_services.sas_description as 'ITEM', 
	room_sales.unit_cost as 'UNIT', room_sales.qty as 'QTY', (room_sales.unit_cost * room_sales.qty) as 'TOTAL COST'
	from room_sales, sas_category, sales_and_services, occupancy, rooms, room_types
	where room_sales.category_id=sas_category.sas_cat_id
	and room_sales.item_id=sales_and_services.sas_id
	and room_sales.occupancy_id=occupancy.occupancy_id
	and occupancy.room_id=rooms.room_id
	and rooms.room_type_id=room_types.room_type_id
	and room_sales.sales_date >= '$startshift'
	and room_sales.sales_date <= '$end'
	and occupancy.actual_checkout <= '$end'
	and room_sales.category_id=2
	order by rooms.door_name
	";

	$arrReport = array(
		'title'    => 'Shogun 1 - Cooperative Sales',
		'aggregates'=> array('TOTAL COST'),
		'subtitle' => date('l, F d, Y',strtotime($startshift )) . '&nbsp;&nbsp;&nbsp;Shift Hrs: '. date('g:i A',strtotime( $startshift)). ' - ' . date('g:i A',strtotime( $end )),
		'sql'	   => $sql	
		);

	$report = new report($arrReport);
	$report->buildReport();
	return $report->html();
}


?>
<style>
	body, h1, h2, h3, h4 {
		margin:0;
		margin-width:0;
		margin-height:0;
		font-size:14px;
	}

	h1 {
		font-size:1em;
	}
	h2 {
		font-size:0.9em;
	}
		.printable, .printable2, .printable3, .printable4, .printable5 {
			font-family: sans-serif;
			font-size: 14px;
			font-weight: 550;
			border-bottom:1px dotted #cccccc;
			}
		.report{
			font-family: sans-serif;
			font-size: 14px ;
			text-align:left;
			font-weight: 550;
				
		}
		.report td{
			padding-bottom:12px;
		}
		.summary{
			font-family: sans-serif;
			font-size: 14px;
			text-align:left;
			font-weight: 550;
		}

		.menubar {
			font-size:12px;
			font-family:arial, helvetica,sans-serif;
			font-weight:bold;
			list-style:none;
			background-color:#dddddd;
			padding:4px;
			margin:0;
		}
		.menubar li {
			display:inline;
			border:1px solid #eeeeee;
			background-color:#ececec;
			padding:2px;
			padding-left:8px;
			padding-right:8px;
		}

		.menubar li:hover {
			background-color:#ffffff;
		}

		.toolbar {
			background-color:#cccccc;
			padding:0px;
		}

		.aggregates th {
			border-top:1px solid #111111;
		}

		.recordcount {
			padding-top:24px;
			padding-bottom:12px;
		}
</style>
<script src="../js/jquery.js" type="text/javascript"></script>
<script type="text/javascript" src="../js/jquery.print.js"></script>
<script type="text/javascript">
 
		// When the document is ready, initialize the link so
		// that when it is clicked, the printable area of the
		// page will print.
		$(document).ready(function(){	
			$("#printall").click(function(){
				$(".printable4").print();
				$(".printable3").print();
				$(".printable2").print();
				$(".printable").print(); 
				return( false );
			});

			$("#printcheckoutdetails").click(function(){
				$(".printable").print(); 
				return( false );
			});

			$("#printcheckoutsummary").click(function(){
				$(".printable2").print();
				return( false );
			});

			$("#printrechit").click(function(){
				$(".printable3").print();
				return( false );
			});
		
		});
 
</script>
<form name=myform method=post>
<div class="toolbar">

<ul class="menubar">
Select Shift: <? echo getLatestShifts($shiftid); ?> &nbsp;&nbsp;&nbsp;&nbsp; 
Print
<li><a href="#" id='printall'>All</a></li>
<li><a href="#" id='printcheckoutdetails'>Checkout Details</a></li>
<li><a href="#" id='printcheckoutsummary'>Checkout Summary</a></li>
<li><a href="#" id='printrechit'>Rechit Details, Summary & Coop</a></li>
</ul>
</div>
<br>

<br>
<br>
<div class='printable'>
<? echo getCheckoutReport($startshift,$end,$suser_id,$euser_id,$lobbyid); ?>
</div>
<br>
<br>
<br>
<div class='printable2'>
<? echo getCheckoutSummary($startshift,$end,$suser_id,$euser_id,$lobbyid); ?>
</div>
<br />
<br>
<br>
<div class='printable3'>
<? echo getRechitDetailReport($startshift,$end,$suser_id,$euser_id,$lobbyid); ?>
<br>
<br>
<br>
<? echo getRechitSummary($startshift,$end,$suser_id,$euser_id,$lobbyid); ?>
<br>
<br>
<br>
<? echo getCoopSummary($startshift,$end,$suser_id,$euser_id,$lobbyid); ?>
</div>

</form>